package dataAccessLayer.factoryMySQL.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import dataAccessLayer.abstractFactory.daos.DAOrecipes;
import dataAccessLayer.dataBase.DBManager;
import dataAccessLayer.visualObject.VOrecipe;

public class DAOrecipesMySQL implements DAOrecipes
{
	private DBManager dbManager;

	public DAOrecipesMySQL(DBManager dbManager) 
	{
		this.dbManager = dbManager;
	}

	public int insertRecipe(VOrecipe vOrecipe) 
	{
		int code = -1;

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		Connection connection = dbManager.getConnection();

		try 
		{
			preparedStatement = connection.prepareStatement("insert into recipes (name, image, description) VALUES (?,?,?)");

			preparedStatement.setString(1, vOrecipe.getName());
			preparedStatement.setString(2, vOrecipe.getImage());
			preparedStatement.setString(3, vOrecipe.getDescription());

			preparedStatement.execute();

			preparedStatement = connection.prepareStatement("SELECT code FROM recipes ORDER BY code DESC LIMIT 1");

			resultSet = preparedStatement.executeQuery();
			resultSet.next();

			code = resultSet.getInt(1);
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
				resultSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}

		return code;
	}

	public boolean updateRecipe(VOrecipe vOrecipe) 
	{
		boolean updated = false;

		PreparedStatement preparedStatement = null;

		Connection connection = dbManager.getConnection();

		try 
		{
			preparedStatement = connection.prepareStatement("update recipes set name=?, image=?, description=? where code=?");

			preparedStatement.setString(1, vOrecipe.getName());
			preparedStatement.setString(2, vOrecipe.getImage());
			preparedStatement.setString(3, vOrecipe.getDescription());
			preparedStatement.setInt(4, vOrecipe.getCode());

			preparedStatement.execute();

			updated = true;

		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return updated;
	}

	public boolean removeRecipe(VOrecipe vOrecipe) 
	{
		boolean removed = false;

		PreparedStatement preparedStatement = null;

		Connection connection = dbManager.getConnection();

		try
		{
			preparedStatement = connection.prepareStatement("delete from recipes where code=?");

			preparedStatement.setInt(1, vOrecipe.getCode());
			preparedStatement.execute();

			removed = true;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}

		return removed;
	}

	public ArrayList<VOrecipe> listRecipes() 
	{
		ArrayList<VOrecipe>recipes = new ArrayList<VOrecipe>();

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		Connection connection = dbManager.getConnection();

		try 
		{
			preparedStatement = connection.prepareStatement("SELECT code,name, image, description FROM recipes");

			resultSet = preparedStatement.executeQuery();

			while(resultSet.next())
			{
				VOrecipe recipe = new VOrecipe();

				recipe.setCode(resultSet.getInt(1));
				recipe.setName(resultSet.getString(2));
				recipe.setImage(resultSet.getString(3));
				recipe.setDescription(resultSet.getString(4));

				recipes.add(recipe);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
				resultSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}

		return recipes;
	}

	public VOrecipe searchRecipeByCode(VOrecipe vOrecipe) 
	{
		VOrecipe recipe = null;

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		Connection connection = dbManager.getConnection();

		try 
		{
			preparedStatement = connection.prepareStatement("SELECT code,name, image, description FROM recipes WHERE code=?");

			preparedStatement.setInt(1, vOrecipe.getCode());

			resultSet = preparedStatement.executeQuery();
			resultSet.next();

			recipe = new VOrecipe();

			recipe.setCode(resultSet.getInt(1));
			recipe.setName(resultSet.getString(2));
			recipe.setImage(resultSet.getString(3));
			recipe.setDescription(resultSet.getString(4));

		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}

		return recipe;
	}

	public VOrecipe searchRecipeByName(VOrecipe vOrecipe)
	{
		VOrecipe recipe = null;

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		Connection connection = dbManager.getConnection();

		try 
		{
			preparedStatement = connection.prepareStatement("SELECT code,name, image, description FROM recipes WHERE name=?");

			preparedStatement.setString(1, vOrecipe.getName());

			resultSet = preparedStatement.executeQuery();
			resultSet.next();

			recipe = new VOrecipe();

			recipe.setCode(resultSet.getInt(1));
			recipe.setName(resultSet.getString(2));
			recipe.setImage(resultSet.getString(3));
			recipe.setDescription(resultSet.getString(4));

		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}

		return recipe;
	}

}
